import yaml
import sqlite3
import csv
import datetime
import calendar
import re
import threading
from threading import Thread
from time import gmtime,strftime,time,sleep,ctime,clock
from Queue import Queue

class OracleRobot:
    
    config = yaml.load(open('config.yaml'))
    conShare = sqlite3.connect(config['development']['database_share'])
    conOracle = sqlite3.connect(config['development']['database_oracle'])
    conMemory = sqlite3.connect(config['development']['database_memory'])
    
    cursorShare = conShare.cursor()
    cursorOracle = conOracle.cursor()
    cursorMemory = conMemory.cursor()    
    
    tbShare = {
        'symbols':'symbols',
        'schema':'schema_migrations',
        'urls':'urls',
        'proxys':'proxys',
        'trades':'trades'        
        }
    tbMemory = {
        'symbols':'symbols',
        'trades':'trades'        
        }
    tbOracle = {
        'markov':'markov',
        'pro_rise_of_day':'pro_rise_of_day',
        'benefit':'benefit',
        'delta_of_month':'delta_of_month'
        }
    change = {
        'Fall':'0',
        'Rise':'1',
        'Flat':'2'
        }
    today = datetime.date.today().strftime("%Y%m%d")
    start_time = 20000101
    end_time = today    
    run_start = clock()
    run_finish = clock()
    symbols = []
    trades = []
    
    def __init__(self):
        try:
            if True == self.checkShareDbIsExist():                
                self.initOracle()
                self.loadShareToMemory()
                self.getAllSymbols()
            else:
                print "************* Share Database Is Not Exist! *************"
        except Exception,e:
            print e
        
    def initOracle(self):
        try:
            """
            Init Oracle Database
            """
            self.cursorOracle.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                symbol varchar(25) UNIQUE,
                P_00 float,            
                P_01 float,
                P_02 float,
                P_10 float,
                P_11 float,
                P_12 float,
                P_20 float,
                P_21 float,
                P_22 float
                )
                """
                %(self.tbOracle['markov'])
                )
            
            self.cursorOracle.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                symbol varchar(25),
                MON float,            
                TUES float,
                WED float,
                THUR float,
                FRI float,                
                period varchar(5)
                )
                """
                %(self.tbOracle['pro_rise_of_day'])
                )
            
            self.cursorOracle.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                symbol varchar(25),
                highest float,               
                lowest float,
                current float,
                benefit_period float,
                period varchar(10)
                )
                """
                %(self.tbOracle['benefit'])
                )
            self.cursorOracle.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                symbol varchar(25),
                highest_date varchar(10),               
                lowest_date varchar(10),
                delta_of_month varchar(10),
                year float varchar(10)
                )
                """
                %(self.tbOracle['delta_of_month'])
                )
            """            
            Commit
            """
            self.conOracle.commit()
        
        except Exception,e:
            print e
            
    def checkShareDbIsExist(self):
        try:
            self.cursorShare.execute( 
            """
            SELECT COUNT(*) FROM %s           
            """
            %(self.tbShare['symbols'])
            )
            num = self.cursorShare.fetchone()[0]
            if 0 != num:
                return True
            else:
                return False             
        except Exception,e:
            print e
            
    def loadShareToMemory(self):
        try:
            """
            Init Memory Database
            """
            self.cursorMemory.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                symbol varchar(25) UNIQUE,
                name varchar(30),            
                exchange varchar(10)
                )
                """
                %(self.tbMemory['symbols'])
                )
            
            self.cursorMemory.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                symbol varchar(25),
                date datetime,
                opening float,
                closeing float,            
                low float,
                high float,
                volume int
                )          
                """
                %(self.tbMemory['trades'])
                )
            """
            Commit
            """
            self.conMemory.commit()

            """
            Copy share data
            """
            self.copyShareData()  
           
            
        except Exception,e:
            print e
            
    def copyShareData(self):
        try:
            symbols = None
            trades = None
            """
            Copy table of symbols to memory
            """
            self.cursorShare.execute(  
                """
                SELECT * FROM %s 
                """            
                %(self.tbShare['symbols'])
                )
            symbolsData = self.cursorShare.fetchall()
            for data in symbolsData:
                self.cursorMemory.execute(
                """
                INSERT INTO %s(symbol,name,exchange) VALUES (?,?,?)
                """
                %(self.tbMemory['symbols'])
                ,data
                )
            self.conMemory.commit()
            
            """
            Copy table of trades to memory
            """
            self.cursorShare.execute(  
                """
                SELECT * FROM %s
                """            
                %(self.tbShare['trades'])
                )            
            tradesData = self.cursorShare.fetchall()
            for data in tradesData:
                self.cursorMemory.execute(
                """
                INSERT INTO %s(symbol,date,opening,closeing,low,high,volume) VALUES (?,?,?,?,?,?,?)
                """
                %(self.tbMemory['trades'])
                ,data
                )
            self.conMemory.commit()
            self.run_finish = clock()
            print "************* Share Data Have been Copyed *************"
            print "************* Copy Time: " + str(self.run_finish - self.run_start) + " (s) *************"
        
        except Exception,e:
            print e            
            
    def getAllSymbols(self):
        try:
            self.cursorMemory.execute(  
                """
                SELECT symbol FROM %s
                """            
                %(self.tbMemory['symbols'])
                )
            symbols =  self.cursorMemory.fetchall()
            for symbol in symbols:
                self.symbols.append(symbol[0])
            return True
        except Exception,e:
            print e
    
    def getOneTrade(self,symbol=''):
        try:
            self.cursorMemory.execute(  
                """
                SELECT * FROM %s WHERE symbol = '%s' AND volume!=0 ORDER BY date ASC
                """            
                %(self.tbMemory['trades'],symbol)
                )
            trade = self.cursorMemory.fetchall()
            return trade
        except Exception,e:
            print e
            
    def getAllTrades(self):
        try:
            self.run_start = clock()
            for symbol in self.symbols:
                self.trades.append(self.getOneTrade(symbol))
            self.run_finish = clock()
            print "************* All Trades Have Been Loaded *************"
            print "************* Load Time: " + str(self.run_finish - self.run_start) + " (s) *************"
        except Exception,e:
            print e
            
    def getMarkovChain(self,symbol):
        try:
            chain = []
            trade = self.getOneTrade(symbol)
            for day_trade in trade:
                if 0 == self.getRiseFallFlat(day_trade):
                    chain.append('0')
                elif 1 == self.getRiseFallFlat(day_trade):
                    chain.append('1')
                elif 2 == self.getRiseFallFlat(day_trade):
                    chain.append('2')
                else:
                    continue
            return chain                
                
        except Exception,e:
            print e            
                   
    def getRiseFallFlat(self,day_trade):
        try:
            opening = day_trade[2]
            closeing = day_trade[3]
            delta = float(closeing) - float(opening)
            if delta < 0:
                return 0
            elif delta > 0:
                return 1
            else:
                return 2
        except Exception,e:
            print e
            
    def getStepTransitionProbability(self,symbol):
        try:
            number_of_change = [0,0,0,0,0,0,0,0,0]
            step_transition_probability = [0,0,0,0,0,0,0,0,0]
            STP = None
            chain = self.getMarkovChain(symbol)
            number_of_samples = len(chain)
            number_of_fall = 0
            number_of_rise = 0
            number_of_flat = 0
            number_of_statics = 0
            for i in range(0,number_of_samples-1):
                if ['0','0'] == chain[i:i+2]:
                    number_of_change[0] = number_of_change[0]+1
                elif ['0','1'] == chain[i:i+2]:
                    number_of_change[1] = number_of_change[1]+1
                elif ['0','2'] == chain[i:i+2]:
                    number_of_change[2] = number_of_change[2]+1
                elif ['1','0'] == chain[i:i+2]:
                    number_of_change[3] = number_of_change[3]+1
                elif ['1','1'] == chain[i:i+2]:
                    number_of_change[4] = number_of_change[4]+1
                elif ['1','2'] == chain[i:i+2]:
                    number_of_change[5] = number_of_change[5]+1
                elif ['2','0'] == chain[i:i+2]:
                    number_of_change[6] = number_of_change[6]+1
                elif ['2','1'] == chain[i:i+2]:
                    number_of_change[7] = number_of_change[7]+1
                elif ['2','2'] == chain[i:i+2]:
                    number_of_change[8] = number_of_change[8]+1
                else:
                    continue
                number_of_fall = number_of_change[0] + number_of_change[1] + number_of_change[2]
                number_of_rise = number_of_change[3] + number_of_change[4] + number_of_change[5]
                number_of_flat = number_of_change[6] + number_of_change[7] + number_of_change[8]
            for i in range(0,8):
                if 0 == i or 1 == i or 2 == i:
                    number_of_statics = number_of_fall
                elif 3 == i or 4 == i or 5 == i:
                    number_of_statics = number_of_rise
                elif 6 == i or 7 == i or 8 == i:
                    number_of_statics = number_of_flat
                else:
                    number_of_statics = 0
                if 0 != float(number_of_statics):                    
                    step_transition_probability[i] =  round(float(number_of_change[i])/float(number_of_statics),4)                      
                else:
                    continue            
            STP = [symbol] + step_transition_probability
            self.cursorOracle.execute(
                """
                INSERT INTO %s VALUES (?,?,?,?,?,?,?,?,?,?)
                """
                %(self.tbOracle['markov'])
                ,STP
                )
            
        except Exception,e:
            print e
            
    def cleanSTP(self):
        try:
            self.cursorOracle.execute(
                """
                DELETE FROM  %s WHERE P_00 = 0 OR P_01 = 0 OR P_02 = 0 OR P_10 = 0 OR P_11 = 0 OR P_12 =0 OR P_20 = 0 OR P_21 = 0 
                """
                %(self.tbOracle['markov'])
                )
            self.conOracle.commit()
        except Exception,e:
            print e   
            
    def setMarkov(self):
        try:            
            self.run_start = clock()
            number_of_records = self.getRecNum('markov')
            if 0 == number_of_records:            
                counter = 0
                for symbol in self.symbols:
                    counter = counter + 1
                    self.getStepTransitionProbability(symbol)
                    if 0 == counter%200:                    
                        self.conOracle.commit()
                        print "************* " + str(counter) + " STPS Have Been Setted *************"
                    else:
                        pass
                self.conOracle.commit()
            else:
                pass
            self.cleanSTP()
            self.run_finish = clock()
            print "************* All STPS Have Been Setted *************"
            print "************* It takes: " + str(self.run_finish - self.run_start) + " (s) *************"         
        
        except Exception,e:
            print e
            
    def getRecNum(self,table):
        try:
            self.cursorOracle.execute(            
            """
            SELECT count(*) FROM %s
            """
            %(table)
            )
            return self.cursorOracle.fetchone()[0]
        except Exception,e:
            print e
            
    def getNumOfTradeDays(self,period = '3Y'):
        try:
            number_of_trade_days = 0
            number_of_days_1week = 5
            number_of_weeks_1year = 52
            number_of_weeks_1month = 4
            if '3Y' == period:
                number_of_trade_days = number_of_days_1week * number_of_weeks_1year * 3
            elif '2Y' == period:
                number_of_trade_days = number_of_days_1week * number_of_weeks_1year * 2
            elif '1Y' == period:
                number_of_trade_days = number_of_days_1week * number_of_weeks_1year * 1
            elif '6M' == period:
                number_of_trade_days = number_of_days_1week * number_of_weeks_1month * 6
            elif '3M' == period:
                number_of_trade_days = number_of_days_1week * number_of_weeks_1month * 3
            elif '1M' == period:
                number_of_trade_days = number_of_days_1week * number_of_weeks_1month * 1
            else:
                pass
            return number_of_trade_days
        except Exception,e:
            print e
            
           
    def getBenefit(self,symbol=''):
        try:
            trades = self.getOneTrade(symbol)
            for loop in range(0,6):
                days = 0
                highest = 0
                lowest = 0
                current = 0
                high = []
                low = []
                benefit = []
                if 0 == loop:
                    days = self.getNumOfTradeDays('3Y')
                    period = '3Y'
                elif 1 == loop:
                    days = self.getNumOfTradeDays('2Y')
                    period = '2Y'
                elif 2 == loop:
                    days = self.getNumOfTradeDays('1Y')
                    period = '1Y'
                elif 3 == loop:
                    days = self.getNumOfTradeDays('6M')
                    period = '6M'
                elif 4 == loop:
                    days = self.getNumOfTradeDays('3M')
                    period = '3M'
                elif 5 == loop:
                    days = self.getNumOfTradeDays('1M')
                    period = '1M'
                else:
                    pass
                if 0 != days:
                    start = len(trades)- days
                    trade_period = trades[start:]
                    for trade in trade_period:                        
                        high.append(trade[5])
                        low.append(trade[4])
                    if [] != high and [] != low: 
                        highest = max(high)
                        lowest = min(low)
                        current = trades[len(trades)-1][3]    
                        benefit_period = round(float((highest - current)/current),4)
                        benefit = [symbol,highest,lowest,current,benefit_period,period]                                
                        self.cursorOracle.execute(
                            """
                            INSERT INTO %s VALUES (?,?,?,?,?,?)
                            """
                            %(self.tbOracle['benefit'])
                            ,benefit
                            )  
                    else:
                        pass                 
                else:
                    continue                                
        except Exception,e:
            print e
    
            
    def setBenefit(self):
        try:
            self.run_start = clock()
            number_of_records = self.getRecNum('benefit')
            if 0 == number_of_records:            
                counter = 0
                for symbol in self.symbols:
                    counter = counter + 1
                    self.getBenefit(symbol)
                    if 0 == counter%200:                    
                        self.conOracle.commit()
                        print "************* " + str(counter) + " symbols have been setted *************"
                    else:
                        pass
                self.conOracle.commit()
            else:
                pass
            self.run_finish = clock()
            print "************* All Benefits Have Been Setted *************"
            print "************* It Takes: " + str(self.run_finish - self.run_start) + " (s) *************"           
        except Exception,e:
            print e
            
           
    def getProRiseOfDay(self,symbol=''):
        try:
            day = []
            trades = self.getOneTrade(symbol)
            chain = self.getMarkovChain(symbol)
            for trade in trades:
                day.append(trade[1])
            for loop in range(0,2):
                
                number_of_weekday = [0,0,0,0,0]
                number_of_rise = [0,0,0,0,0]
                weekday = [0,0,0,0,0]                
                ProRiseOfDay = []
                
                if 0 == loop:
                    days = self.getNumOfTradeDays('3Y')
                    period = '3Y'
                elif 1 == loop:
                    days = self.getNumOfTradeDays('1Y')
                    period = '1Y'
                else:
                    pass
                start = len(chain)- days
                chain_period = chain[start:]
                day_period = day[start:]
                for i in range(0,len(day_period)):
                    dayParam = day_period[i].split('-')
                    if 1 == datetime.date(int(dayParam[0]),int(dayParam[1]),int(dayParam[2])).isoweekday():
                        number_of_weekday[0] = number_of_weekday[0] + 1
                        if '1' == chain_period[i]:
                            number_of_rise[0] = number_of_rise[0] + 1
                        else:
                            pass
                    elif 2 == datetime.date(int(dayParam[0]),int(dayParam[1]),int(dayParam[2])).isoweekday():
                        number_of_weekday[1] = number_of_weekday[1] + 1
                        if '1' == chain_period[i]:
                            number_of_rise[1] = number_of_rise[1] + 1
                        else:
                            pass
                    elif 3 == datetime.date(int(dayParam[0]),int(dayParam[1]),int(dayParam[2])).isoweekday():
                        number_of_weekday[2] = number_of_weekday[2] + 1
                        if '1' == chain_period[i]:
                            number_of_rise[2] = number_of_rise[2] + 1
                        else:
                            pass
                    elif 4 == datetime.date(int(dayParam[0]),int(dayParam[1]),int(dayParam[2])).isoweekday():
                        number_of_weekday[3] = number_of_weekday[3] + 1
                        if '1' == chain_period[i]:
                            number_of_rise[3] = number_of_rise[3] + 1
                        else:
                            pass
                    elif 5 == datetime.date(int(dayParam[0]),int(dayParam[1]),int(dayParam[2])).isoweekday():
                        number_of_weekday[4] = number_of_weekday[4] + 1
                        if '1' == chain_period[i]:
                            number_of_rise[4] = number_of_rise[4] + 1
                        else:
                            pass
                    else:
                        pass                    
                for j in range(0,5):
                    if 0 != number_of_weekday[j]:
                        weekday[j] = round(float(number_of_rise[j])/float(number_of_weekday[j]),3)
                    else:
                        continue
                ProRiseOfDay = [symbol,weekday[0],weekday[1],weekday[2],weekday[3],weekday[4],period]
                self.cursorOracle.execute(
                    """
                    INSERT INTO %s VALUES (?,?,?,?,?,?,?)
                    """
                    %(self.tbOracle['pro_rise_of_day'])
                    ,ProRiseOfDay
                    )
        except Exception,e:
            print e
    
    def setProRiseOfDay(self):
        try:
            self.run_start = clock()
            number_of_records = self.getRecNum('pro_rise_of_day')
            if 0 == number_of_records:            
                counter = 0
                for symbol in self.symbols:
                    counter = counter + 1
                    self.getProRiseOfDay(symbol)
                    if 0 == counter%200:                    
                        self.conOracle.commit()
                        print "************* " + str(counter) + " symbols have been setted *************"
                    else:
                        pass
                self.conOracle.commit()
            else:
                pass
            self.run_finish = clock()
            print "************* All ProRiseOfDay Have Been Setted *************"
            print "************* It Takes: " + str(self.run_finish - self.run_start) + " (s) *************"           
        except Exception,e:
            print e
            
    def getDeltaOfMonth(self,symbol):
        try:            
            DeltaOfMonth = []
            highest_date = ''
            lowest_date = ''
            trade = self.getOneTrade(symbol)
            if 0 != len(trade):
                first_year = int(trade[0][1].split("-")[0])
                last_year = int(trade[len(trade)-1][1].split("-")[0])
                for year in range(first_year,last_year):
                    self.cursorMemory.execute(
                        """
                        SELECT date,high FROM %s WHERE symbol= '%s' AND date BETWEEN "%s-01-01" AND "%s-12-31" ORDER BY high DESC
                        """
                        %(self.tbMemory['trades'],symbol,str(year),str(year))
                        )
                    record = self.cursorMemory.fetchall()
                    if 0 != len(record):
                        highest_date = record[0][0]
                        lowest_date = record[len(record)-1][0]
                        delta_month = abs(int(highest_date.split("-")[1])-int(lowest_date.split("-")[1]))
                        DeltaOfMonth = [symbol,highest_date,lowest_date,delta_month,year]                             
                        self.cursorOracle.execute(
                            """
                            INSERT INTO %s VALUES (?,?,?,?,?)
                            """
                            %(self.tbOracle['delta_of_month'])
                            ,DeltaOfMonth
                            )
                    else:
                        pass
            else:
                pass

        except Exception,e:
            print e
            
            
    def setDeltaOfMonth(self):
        try:
            self.run_start = clock()
            number_of_records = self.getRecNum('delta_of_month')
            if 0 == number_of_records:            
                counter = 0
                for symbol in self.symbols:
                    counter = counter + 1
                    self.getDeltaOfMonth(symbol)
                    if 0 == counter%200:                    
                        self.conOracle.commit()
                        print "************* " + str(counter) + " symbols have been setted *************"
                    else:
                        pass
                self.conOracle.commit()
            else:
                pass
            self.run_finish = clock()
            print "************* All DeltaOfMonth Have Been Setted *************"
            print "************* It Takes: " + str(self.run_finish - self.run_start) + " (s) *************"           
        except Exception,e:
            print e
            
    def run(self):
        try:            
            self.setMarkov()
            self.setBenefit()
            self.setProRiseOfDay()
            self.setDeltaOfMonth()
        except Exception,e:
            print e